package com.trabalho1.db.dao;

import com.trabalho1.db.ConnectionFactory;
import com.trabalho1.model.ModelEndereco;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DaoEndereco 
{    
    private Connection con;
    private PreparedStatement stmt;
    
    private void executeSQL(String sql) 
    {
        try 
        {
            con  = ConnectionFactory.getConnection();
            stmt = con.prepareStatement(sql);
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(DaoEndereco.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println(ex.getMessage());
        }
    }
    
    private void closeConnection() 
    {
        try 
        {
            stmt.close();
            con.close();
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(DaoEndereco.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println(ex.getMessage());
        }
    }   

    public void insert(ModelEndereco mdlEndereco)
    {  
        this.executeSQL(
            "INSERT INTO endereco ( "
                + "id_usuario, "
                + "tipo, "
                + "cep, "
                + "estado, "
                + "cidade, "
                + "logradouro, "
                + "bairro, "                
                + "numero, "
                + "complemento "
            + ") VALUES ( "
                + "?, "
                + "?, "
                + "?, "
                + "?, "
                + "?, "
                + "?, "
                + "?, "
                + "?, "
                + "? "
            + ")"
        );
        
        try 
        {          
            Integer i = 0;
            
            stmt.setInt(++i, mdlEndereco.getId_usuario());
            stmt.setString(++i, mdlEndereco.getTipo());
            stmt.setString(++i, mdlEndereco.getCep());
            stmt.setString(++i, mdlEndereco.getEstado());
            stmt.setString(++i, mdlEndereco.getCidade());
            stmt.setString(++i, mdlEndereco.getLogradouro());
            stmt.setString(++i, mdlEndereco.getBairro());            
            stmt.setString(++i, mdlEndereco.getNumero());
            stmt.setString(++i, mdlEndereco.getComplemento());
            stmt.execute();   
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(DaoEndereco.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println(ex.getMessage());
        } 
        finally 
        {
            this.closeConnection();
        }
    }
    
    public void update(ModelEndereco mdlEndereco) 
    {
        this.executeSQL(
            "UPDATE endereco SET "
                + "id_usuario = ?, "
                + "tipo = ?, "
                + "cep = ?, "
                + "estado = ?, "
                + "cidade = ?, "
                + "logradouro = ?, "
                + "bairro = ?, "                
                + "numero = ?, "
                + "complemento = ? "
            + "WHERE "
                + "id_endereco = ?"
        );
        
        try 
        {       
            Integer i = 0;
            
            stmt.setInt(++i, mdlEndereco.getId_usuario());
            stmt.setString(++i, mdlEndereco.getTipo());
            stmt.setString(++i, mdlEndereco.getCep());
            stmt.setString(++i, mdlEndereco.getEstado());
            stmt.setString(++i, mdlEndereco.getCidade());
            stmt.setString(++i, mdlEndereco.getLogradouro());
            stmt.setString(++i, mdlEndereco.getBairro());            
            stmt.setString(++i, mdlEndereco.getNumero());
            stmt.setString(++i, mdlEndereco.getComplemento());
            stmt.setInt(++i, mdlEndereco.getId_endereco());
            stmt.execute();            
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(DaoEndereco.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            this.closeConnection();
        }
    }

    public void delete(ModelEndereco mdlEndereco)
    {
        this.executeSQL("DELETE FROM endereco WHERE id_endereco = ?");
        
        try 
        {
            stmt.setInt(1, mdlEndereco.getId_endereco());
            stmt.execute();
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(DaoEndereco.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            this.closeConnection();
        }

    }    
    
    public List<ModelEndereco> findAll(Integer id_usuario) 
    {
        List<ModelEndereco> enderecos = new ArrayList<ModelEndereco>();
        
        String sql = "SELECT * FROM endereco ";
                
        if (id_usuario > 0)
        {
            sql+= "WHERE id_usuario = ? ";
        }
        
        sql+= "ORDER BY id_usuario";
        
        this.executeSQL(sql);
        
        ResultSet rset;
        
        try 
        {
            if (id_usuario > 0)
            {
                stmt.setInt(1, id_usuario);
            }
            
            rset = stmt.executeQuery();
            
            while (rset.next()) 
            {
                ModelEndereco resEndereco = new ModelEndereco();
                resEndereco.setId_endereco(rset.getInt("id_endereco"));
                resEndereco.setId_usuario(rset.getInt("id_usuario"));
                resEndereco.setTipo(rset.getString("tipo"));
                resEndereco.setEstado(rset.getString("estado"));
                resEndereco.setCidade(rset.getString("cidade"));
                resEndereco.setLogradouro(rset.getString("logradouro"));
                resEndereco.setBairro(rset.getString("bairro"));
                resEndereco.setCep(rset.getString("cep"));
                resEndereco.setNumero(rset.getString("numero"));
                resEndereco.setComplemento(rset.getString("complemento"));
                enderecos.add(resEndereco);
            }
            
            rset.close();
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(DaoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            this.closeConnection();
        }
       
        return enderecos;
    }   
    
    public ModelEndereco findOne(ModelEndereco mdlEndereco)
    {
        Integer id_endereco = mdlEndereco.getId_endereco();
        
        this.executeSQL("SELECT * FROM endereco WHERE id_endereco = ? LIMIT 1");
        ResultSet rset;
        
        ModelEndereco resEndereco = new ModelEndereco();
        
        try 
        {
            stmt.setInt(1, id_endereco);
            rset = stmt.executeQuery();
            rset.next();    
            
            resEndereco.setId_endereco(rset.getInt("id_endereco"));
            resEndereco.setId_usuario(rset.getInt("id_usuario"));
            resEndereco.setTipo(rset.getString("tipo"));
            resEndereco.setEstado(rset.getString("estado"));
            resEndereco.setCidade(rset.getString("cidade"));
            resEndereco.setLogradouro(rset.getString("logradouro"));
            resEndereco.setBairro(rset.getString("bairro"));
            resEndereco.setCep(rset.getString("cep"));
            resEndereco.setNumero(rset.getString("numero"));
            resEndereco.setComplemento(rset.getString("complemento"));            
         
            rset.close();
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(DaoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            this.closeConnection();
        }
        
        return resEndereco;
    }
}